The present invention relates to database systems, and more specifically, to the detection and correction of hot pages in a database system.
In database systems, such as relational database management systems, data is stored in pages (blocks) that have tables. Each table has rows (records) and columns (fields or attributes). When a page of data is being accessed and/or updated, it is latched in an exclusive state. Read access to a page typically requires the page to be latched in a shared state. As long as the page is latched in exclusive mode, it cannot be accessed or updated by any other process. A share latch may allow reads, but not updates by another process. In many situations, an accessing or updating process must first release the latched page before other processes may access it. When another process or thread attempts to access a latched page, there is said to be “contention” for the latched page. Contentions can be either read-write contention or write-write contention. Such page contention results in delay (also called “sleep states”) as the process or thread wanting to access the latched page waits for the page to be released.
Page contention can significantly degrade overall system performance. A key cause of such contention occurs when: a) two or more rows are stored on a page; b) at least one of these rows is frequently updated; and c) at least one other row is somewhat frequently read or updated by concurrent transactions. When these conditions exist, a page can be a performance bottleneck because the page is latched in an exclusive mode that prevents other processes from accessing it. It often requires much time and effort on the part of administrators to detect and correct such problems when they exist.